'''
Created on Feb 17, 2013

@author: Petko
'''
from sent.model import BaseDBManager
from sent.model.entities.opinion import TextStorage, Opinion, OpinionEvaluations,\
    ViewEnt, ViewOpinion, PendingUpdateDeletes, OpinionState, PendingUpdateState
import datetime
from uuid import UUID
import cStringIO
from sqlalchemy.sql.expression import func, and_, or_, desc
from sent.model.entities.user import GroupBaseOpinionPermissions, User
from sent.utils.attention import AttentionDelta
from sententialyzer.settings import DbSessionRO, DbSession, DEBUG, SENTENTIALYZER_USE_DELAYED_INSERT_DELETE
from sqlalchemy.orm import joinedload
from sent.utils.sent_logger import sent_logger
from sent.utils.update_range import UpdateRagesSet
import sys

logger = sent_logger( __name__ )

class OpinionDBManager(BaseDBManager):
    '''
    classdocs
    '''

    def __init__(self, isReadOnly):
        '''
        Constructor
        '''
        BaseDBManager.__init__(self)
        
        if isReadOnly:
            self.dbSession = DbSessionRO()
        else:
            self.dbSession = DbSession()
        
    def createBaseOpinion(self, opinion):
        
        internal_id = self.generateUUID()
        opinion.setUUID(internal_id)
        
        opinion.base_op_id = opinion.id
        opinion.parent_id = opinion.id
        
        opinion.negative_attention = 0
        opinion.positive_attention = 0
        
        opinion.nested_set_left = 1
        opinion.nested_set_right = 2
        opinion.tree_depth = 0
        
        opinion.op_state = OpinionState.NORMAL
        
        self.insertOpinion(opinion)
        
#        intId = BaseOpinionShortId()
#        intId.setUUID(internal_id)
#        #self.dbSession.add(intId)
    
    def createOpinion(self, opinion, parentOp):
        internal_id = self.generateUUID()
        opinion.setUUID(internal_id)
        
        opinion.parent_id = parentOp.id
        opinion.base_op_id = parentOp.base_op_id
        opinion.default_perm = parentOp.default_perm
        
        opinion.negative_attention = 0
        opinion.positive_attention = 0
        
        opinion.tree_depth = parentOp.tree_depth + 1
        
        if SENTENTIALYZER_USE_DELAYED_INSERT_DELETE:
            opinion.nested_set_left = -1
            opinion.nested_set_right = -1
            opinion.op_state = OpinionState.INSERTED
        else:
            #the parent's right will soon be moved with 2 to the right
            opinion.nested_set_left = parentOp.nested_set_right
            opinion.nested_set_right = opinion.nested_set_left + 1
            
            self.freeSpaceInTree(parentOp)
        
        self.insertOpinion(opinion)
        
#        self.update_parent_stats(parentOp.id,
#                               int(opinion.has_content), 
#                               opinion.evaluation == OpinionEvaluations.POSITIVE,
#                               opinion.evaluation == OpinionEvaluations.NEUTRAL, 
#                               opinion.evaluation == OpinionEvaluations.NEGATIVE)
        
    def insertOpinion(self, opinion):
        assert isinstance(opinion, Opinion)
        
        if not opinion.id:
            internal_id = self.generateUUID()
            opinion.setUUID(internal_id)
        
        if not opinion.creation_time:
            opinion.creation_time = datetime.datetime.utcnow()
        
        opinion.has_content = len(opinion.title) != 0 or len(opinion.textData) != 0
        opinion.prev_eval = OpinionEvaluations.NEUTRAL
        
#        opinion.num_content_subops = 0
#        opinion.num_positive_subops = 0
#        opinion.num_neutral_subops = 0
#        opinion.num_negative_subops = 0
    
        self.dbSession.add(opinion)
        
        self.dbSession.flush()
        
        textStorage = TextStorage()
        textStorage.id = opinion.id
        textStorage.content = opinion.textData
        textStorage.title = opinion.title
        textStorage.external_data = opinion.external_data
        self.dbSession.add(textStorage)
    
    def update_opinion(self, opinion):
        self.dbSession.merge(opinion)
#        deltas = Deltas()
#        if opinion.evaluation != newEval:
#            if opinion.evaluation != OpinionEvaluations.NEUTRAL:
#                pathToOpinion = self.getPathToOpinion(opinion)
#                
    def update_op_text_data(self, textStorage):
        self.dbSession.merge(textStorage)
    
    def deleteBaseOpinion(self, opinionIdStr):
        aUuid = UUID(opinionIdStr)
        opinionsQ = self.dbSession.query(Opinion.id).filter(Opinion.base_op_id == aUuid.bytes)
        self.dbSession.query(TextStorage).filter(TextStorage.id.in_(opinionsQ.subquery())).delete(synchronize_session=False)
        
        self.dbSession.flush()
        
        self.dbSession.query(GroupBaseOpinionPermissions).filter(GroupBaseOpinionPermissions.opinion_id == aUuid.bytes).delete(synchronize_session=False)
        
        self.dbSession.flush()
        
        opinionsQ.delete(synchronize_session=False)
        
        self.dbSession.flush()
    
    def delete_opinion(self, opinion, deleteOp):
        
        if not SENTENTIALYZER_USE_DELAYED_INSERT_DELETE:
            if (deleteOp):
                self.shrinkTreeForOpinionDelete(opinion)
        
        self.dbSession.query(TextStorage).filter(TextStorage.id == opinion.id).delete(synchronize_session=False)
        
        if not deleteOp or not SENTENTIALYZER_USE_DELAYED_INSERT_DELETE:
            self.dbSession.flush()
        
        if deleteOp:
            if SENTENTIALYZER_USE_DELAYED_INSERT_DELETE:
                if opinion.op_state==OpinionState.NORMAL or opinion.op_state==OpinionState.WAITING_UPDATE:
                    if opinion.op_state==OpinionState.WAITING_UPDATE:
                        opinion.evaluation = opinion.prev_eval
                    opinion.op_state=OpinionState.WAITING_DELETE
                    
                    self.dbSession.merge(opinion)
                    self.dbSession.flush()
                elif opinion.op_state==OpinionState.INSERTED:
                    self.dbSession.delete(opinion)
                    self.dbSession.flush()
                
            else:
                self.dbSession.delete(opinion)
            
#            self.update_parent_stats(opinion.parent_id,
#                                   -int(opinion.has_content),
#                                   -(opinion.evaluation == OpinionEvaluations.POSITIVE),
#                                   -(opinion.evaluation == OpinionEvaluations.NEUTRAL), 
#                                   -(opinion.evaluation == OpinionEvaluations.NEGATIVE))
    
    def batch_delete_opinions(self, opinion_ids):
        if len(opinion_ids) > 0:
            self.dbSession.query(Opinion).filter(Opinion.id.in_(opinion_ids)).delete(synchronize_session=False)
            self.dbSession.flush()
    
    def update_parent_stats(self, parent_op_id, content_subops_delta, positive_subops_delta, neutral_subops_delta, negative_subops_delta):
        pass
#        updateColumns = {}
#        if content_subops_delta:
#            updateColumns[Opinion.num_content_subops] = Opinion.num_content_subops + content_subops_delta
#        
#        if positive_subops_delta:
#            updateColumns[Opinion.num_positive_subops] = Opinion.num_positive_subops + positive_subops_delta
#        
#        if negative_subops_delta:
#            updateColumns[Opinion.num_negative_subops] = Opinion.num_negative_subops + negative_subops_delta
#            
#        if neutral_subops_delta:
#            updateColumns[Opinion.num_neutral_subops] = Opinion.num_neutral_subops + neutral_subops_delta
#        
#        assert len(updateColumns)
#        self.dbSession.query(Opinion).filter(Opinion.id == parent_op_id).update(updateColumns, synchronize_session=False)
    
    def get_base_op_id_str(self, op_uuid_str): 
        q = self.dbSession.query(Opinion.base_op_id).filter(Opinion.id == UUID(op_uuid_str).bytes)
        return q.first()
    
    def get_opinion_by_id_str(self, opId, lockmode = False, loadCreator = False, allowed_states = None):
        aUuid = UUID(opId)
        q = self.dbSession.query(Opinion)
        if loadCreator:
            q = q.options(joinedload(Opinion.creator))
        
        q = q.filter(Opinion.id == aUuid.bytes)
        
        if allowed_states:
            q = q.filter(Opinion.op_state.in_(allowed_states))
        
        if lockmode:
            q = q.with_lockmode(lockmode)
        return q.first()
    
    def getOpinionTextData(self, op_id):
        aUuid = UUID(op_id)
        q = self.dbSession.query(TextStorage)
        return q.filter(TextStorage.id == aUuid.bytes).first()
    
    def get_non_neutral_op_per_usr_bop(self, creator_id, base_op_id):
        q = self.dbSession.query(Opinion)
        q = q.filter(Opinion.base_op_id == base_op_id, 
                     Opinion.creator_id == creator_id,
                     Opinion.evaluation != OpinionEvaluations.NEUTRAL, 
                     or_(Opinion.op_state == OpinionState.NORMAL,
                         Opinion.op_state == OpinionState.WAITING_UPDATE,
                         Opinion.op_state == OpinionState.INSERTED))
        return q.first()
    
    def get_path_to_opinions_safe(self, forUpdate, opinion1, opinion2 = None, loadCreators = False):
        opinion1_or_parent = opinion1
        if opinion1.op_state == OpinionState.INSERTED or not opinion1.has_content:
            opinion1_or_parent = self.get_opinion_by_id_str(str(opinion1.getParentUUID()), loadCreator=loadCreators)
        
        opinion2_or_parent = opinion2
        if opinion2 and (opinion2.op_state == OpinionState.INSERTED or not opinion2.has_content):
            opinion2_or_parent = self.get_opinion_by_id_str(str(opinion2.getParentUUID()), loadCreator=loadCreators)
        
        result = self.get_path_to_opinions(forUpdate, opinion1_or_parent, opinion2_or_parent, loadCreators)
        
#        if opinion1.op_state == OpinionState.INSERTED or not opinion1.has_content:
#            result.append(opinion1_or_parent)
#        
#        if opinion2 and (opinion2.op_state == OpinionState.INSERTED or not opinion2.has_content):
#            result.append(opinion2_or_parent)
        
        return result
        
    def get_path_to_opinions(self, forUpdate, opinion1, opinion2 = None, loadCreators = False):
        assert (not opinion2) or opinion1.base_op_id == opinion2.base_op_id
        q = self.dbSession.query(Opinion)
        
        if loadCreators:
            q = q.options(joinedload(Opinion.creator))
        
        q = q.filter(Opinion.base_op_id == opinion1.base_op_id)
        
        if opinion2:
            q = q.filter(or_(and_(Opinion.nested_set_left <= opinion1.nested_set_left,
                                  Opinion.nested_set_right >= opinion1.nested_set_right),
                             and_(Opinion.nested_set_left <= opinion2.nested_set_left,
                                  Opinion.nested_set_right >= opinion2.nested_set_right)))
        else:
            q = q.filter(Opinion.nested_set_left <= opinion1.nested_set_left,
                         Opinion.nested_set_right >= opinion1.nested_set_right)
        
        if forUpdate:
            q = q.with_lockmode('update')
            
        q = q.order_by(Opinion.nested_set_left)
        
        return q.all()
    
    def getPathFromOpToOp(self, parentOp, childOp):
        assert isinstance(parentOp, Opinion)
        assert isinstance(childOp, Opinion)
        assert parentOp.base_op_id == childOp.base_op_id
        assert parentOp.nested_set_left < childOp.nested_set_left and parentOp.nested_set_right > childOp.nested_set_right
        
        q = self.dbSession.query(Opinion)
        
        q = q.options(joinedload(Opinion.creator))
        
        q = q.filter(Opinion.base_op_id == parentOp.base_op_id)
        
        q = q.filter(Opinion.nested_set_left > parentOp.nested_set_left,
                     Opinion.nested_set_right < parentOp.nested_set_right,
                     Opinion.nested_set_left < childOp.nested_set_left,
                     Opinion.nested_set_right > childOp.nested_set_right)
        
        q = q.order_by(Opinion.nested_set_left)
        
        return q.all()
    
    def apply_attention_deltas(self, deltas):
        deltasMap = {}
        for delta in deltas:
            assert isinstance(delta, AttentionDelta)
            if delta.positive_attention_delta != 0 or delta.negative_attention_delta != 0:
                mapKey = (delta.positive_attention_delta, delta.negative_attention_delta)
                existingEntry = deltasMap.get(mapKey, None)
                if existingEntry:
                    existingEntry.append(delta)
                else:
                    deltasMap[mapKey] = [delta]
        
        for currentDeltas in deltasMap.itervalues():
            #idsList = [op.op_id ]
            query = cStringIO.StringIO()
            
            query.write("UPDATE Opinion " \
                + " SET positive_attention=positive_attention + :positiveDelta," \
                + " negative_attention=negative_attention + :negativeDelta" \
                + " WHERE op_id IN (")
            
            params = {'positiveDelta': currentDeltas[0].positive_attention_delta,
                 'negativeDelta': currentDeltas[0].negative_attention_delta}
            
            opIndex = 0
            for op in currentDeltas:
                if opIndex > 0:
                    query.write(', ')
                paramKey = 'op' + str(opIndex)
                query.write(':' + paramKey)
                opIndex += 1
                params[paramKey] = op.op_id
            query.write(')')
            
            self.dbSession.execute(query.getvalue(), params)
            
            query.close()
        
    def addBaseOpinionPermissions(self, opinionId, groupPermissionDict):
        self.dbSession.flush()
        for groupExtId, permissionSet in groupPermissionDict.iteritems():
            for perm in permissionSet:
                self.dbSession.execute("INSERT INTO Group_Base_Opinion_Permissions (group_id, opinion_id, permission_id)" 
                    + " SELECT "
                    + " (SELECT id FROM UGroup WHERE external_id=:group_ext_id ),"
                    + " :opinion_id,"
                    + " (SELECT permission_id FROM Permission WHERE permission_name=:perm_name) ", 
                    {'group_ext_id': groupExtId, 'opinion_id': opinionId, 'perm_name': perm})
                    
        
    def addViewPermissions(self, viewId, groupPermissionDict):
        self.dbSession.flush()
        for groupExtId, permissionSet in groupPermissionDict.iteritems():
            for perm in permissionSet:
                self.dbSession.execute("INSERT INTO Group_View_Permissions (group_id, view_id, permission_id)" 
                    + " SELECT "
                    + " (SELECT id FROM UGroup WHERE external_id=:group_ext_id),"
                    + " :view_id,"
                    + " (SELECT permission_id FROM Permission WHERE permission_name=:perm_name) ", 
                    {'group_ext_id': groupExtId, 'view_id': viewId, 'perm_name': perm})
                
    def freeSpaceInTree(self, parentOp):
        #http://www.sitepoint.com/hierarchical-data-database-3/
        assert isinstance(parentOp, Opinion)
        
        result = self.dbSession.execute("UPDATE Opinion SET nested_set_right=nested_set_right+2 WHERE nested_set_right>=:parentRight AND base_op_id=:baseOp",
                               {'parentRight': parentOp.nested_set_right, 'baseOp': parentOp.base_op_id})

#        result = self.dbSession.execute("UPDATE Opinion SET nested_set_right=nested_set_right+2 WHERE " 
#                                        + "nested_set_right >= "
#                                        #MySQL power! Yeah! Facepalm!
#                                        + "(SELECT x.nested_set_right FROM (SELECT nested_set_right FROM Opinion WHERE op_id=:parentId) AS x) " 
#                                        + "AND base_op_id=:baseOp",
#                               {'parentId': parentOp.id, 'baseOp': parentOp.base_op_id})
        
        if DEBUG:
            logger.log(sent_logger.DEBUG, "expanding nested_set_right %d where nested_set_right > %d", result.rowcount, parentOp.nested_set_right)
        
        result = self.dbSession.execute("UPDATE Opinion SET nested_set_left=nested_set_left+2 WHERE nested_set_left>:parentRight AND base_op_id=:baseOp",
                               {'parentRight': parentOp.nested_set_right, 'baseOp': parentOp.base_op_id})

#        result = self.dbSession.execute("UPDATE Opinion SET nested_set_left=nested_set_left+2 WHERE "
#                                        + "nested_set_left > "
#                                        + " (SELECT x.nested_set_right FROM (SELECT nested_set_right FROM Opinion WHERE op_id=:parentId) AS x) "
#                                        + "AND base_op_id=:baseOp",
#                               {'parentId': parentOp.id, 'baseOp': parentOp.base_op_id})
        
        if DEBUG:
            logger.log(sent_logger.DEBUG, "expanding nested_set_left %d where nested_set_left > %d", result.rowcount, parentOp.nested_set_right)
    
    def shrinkTreeForOpinionDelete(self, opinion):

        result = self.dbSession.execute("UPDATE Opinion SET nested_set_right=nested_set_right-2 WHERE nested_set_right>:opleft AND base_op_id=:baseOp",
                                       {'opleft': opinion.nested_set_left, 'baseOp': opinion.base_op_id})

#        result = self.dbSession.execute("UPDATE Opinion SET nested_set_right=nested_set_right-2 WHERE "
#                                        + "nested_set_right > "
#                                        + " (SELECT x.nested_set_left FROM (SELECT nested_set_left FROM Opinion WHERE op_id=:opId) AS x) "
#                                        + "AND base_op_id=:baseOp",
#                               {'opId': opinion.id, 'baseOp': opinion.base_op_id})
        
        if DEBUG:
            logger.log(sent_logger.DEBUG, "shrinking nested_set_right %d where nested_set_right > %d", result.rowcount, opinion.nested_set_left)
        
        result = self.dbSession.execute("UPDATE Opinion SET nested_set_left=nested_set_left-2 WHERE nested_set_left>:opleft AND base_op_id=:baseOp",
                                       {'opleft': opinion.nested_set_left, 'baseOp': opinion.base_op_id})

#        result = self.dbSession.execute("UPDATE Opinion SET nested_set_left=nested_set_left-2 WHERE "
#                                        + "nested_set_left > "
#                                        + " (SELECT x.nested_set_left FROM (SELECT nested_set_left FROM Opinion WHERE op_id=:opId) AS x) "
#                                        + "AND base_op_id=:baseOp",
#                               {'opId': opinion.id, 'baseOp': opinion.base_op_id})
        
        if DEBUG:
            logger.log(sent_logger.DEBUG, "shrinking nested_set_left %d where nested_set_left > %d", result.rowcount, opinion.nested_set_left)
    
    def apply_update_ranges_set(self, ranges, base_op_id):
        assert isinstance(ranges, UpdateRagesSet)
        
        #iterate the ranges backwards so that the update of the first range will not move a record to another range when creating 
        #(we assume creating is the more common operation)
        #Overlapping will appear when deleting too, which is solved by handling the overlapping regions after the main loop
        
        #this contains the ranges that have overlapping opinions. The opinions IDs are stored in temporary tables 
        postponed_ranges = []
        
        def get_range_temporary_table_name(rng, left_right):
            return "overlapping_op_ids_%d_%d_%s" % (rng.left, rng.right, left_right)
        
        try:
            #TODO temporary tables to contain the ids of the overlapping opinions 
            for rng in reversed(ranges.ranges):
                if rng.offset != 0:
                    range_left = rng.left
                    if rng.offset < 0:
                        #update with "where nested_set_X < >" only those opinions that remain in the current range,
                        #the rest are updated with "where op_id in" statement
                        range_left = rng.left - rng.offset
                        
                        sql_tmp_table_template = "CREATE TEMPORARY TABLE {0} "\
                                + " ENGINE=MEMORY SELECT op_id FROM Opinion WHERE {1}>=:overlappingLeft AND {1}<=:overlappingRight AND base_op_id = :baseOp"
                                
                        params = {'overlappingLeft': rng.left, 'overlappingRight': (range_left - 1), 'baseOp': base_op_id}
                        
                        postponed_ranges.append(rng)
                        
                        #Create a temporary table with the indexes of the opinions whose nested_set_left is in the overlapping range
                        sql = sql_tmp_table_template.format(get_range_temporary_table_name(rng, 'lft'), 'nested_set_left')
                        self.dbSession.execute(sql, params)
                        
                        #Create a temporary table with the indexes of the opinions whose nested_set_left is in the overlapping range
                        sql = sql_tmp_table_template.format(get_range_temporary_table_name(rng, 'rgt'), 'nested_set_right')
                        self.dbSession.execute(sql, params)
                        
                        pass
                    
                    if range_left <= rng.right:
                        sql_template = "UPDATE Opinion SET {0} = {0} + :offset WHERE base_op_id = :baseOp AND {0} >= :range_left "
                        params = {'offset': rng.offset, 'baseOp': base_op_id, 'range_left': range_left}
                        
                        if rng.right < sys.maxint:
                            sql_template += "AND {0} <= :range_right"
                            params['range_right']= rng.right
                        
                        sql = sql_template.format('nested_set_right')
                        result = self.dbSession.execute(sql, params)
                        
                        if DEBUG:
                            logger.log(sent_logger.DEBUG, "Update nested_set_right [%d,%d]:%d rowcount: %d" % (range_left, rng.right, rng.offset, result.rowcount))
                            
                        sql = sql_template.format('nested_set_left')
                        result = self.dbSession.execute(sql, params)
                        
                        if DEBUG:
                            logger.log(sent_logger.DEBUG, "Update nested_set_left [%d,%d]:%d rowcount: %d" % (range_left, rng.right, rng.offset, result.rowcount))
            
            #now update the overlapping opinions
            for rng in postponed_ranges:
                sql_update_template = "UPDATE Opinion SET {1} = {1} + :offset WHERE op_id IN (SELECT op_id FROM {0})"
                
                params = {'offset': rng.offset}
                          
                sql = sql_update_template.format(get_range_temporary_table_name(rng, 'lft'), 'nested_set_left')
                self.dbSession.execute(sql, params)
                
                sql = sql_update_template.format(get_range_temporary_table_name(rng, 'rgt'), 'nested_set_right')
                self.dbSession.execute(sql, params)
                
        finally:
            for rng in postponed_ranges:
                self.dbSession.execute("DROP TEMPORARY TABLE IF EXISTS " + get_range_temporary_table_name(rng, 'lft'))
                self.dbSession.execute("DROP TEMPORARY TABLE IF EXISTS " + get_range_temporary_table_name(rng, 'rgt'))
                
    def get_opinions_for_insert_delete(self):
        q = self.dbSession.query(PendingUpdateDeletes).filter(PendingUpdateDeletes.state == PendingUpdateState.ACTIVE)
        q = q.with_lockmode('update')
        
        pending_ud = q.first()
        
        if pending_ud:
            base_op_id = pending_ud.id
            
            q = self.dbSession.query(Opinion)
            q = q.filter(Opinion.op_state != OpinionState.NORMAL, Opinion.base_op_id==base_op_id)
            
            return pending_ud, q.all()
        else:
            return None, []
    
    def lock_base_op_ro(self, base_op):
        q = self.dbSession.query(PendingUpdateDeletes).filter(PendingUpdateDeletes.id==base_op)
        q = q.with_lockmode('read')
        result = q.all()
        if len(result) > 0: 
            return result[0] 
        else: 
            return None
        
    def set_base_op_for_update(self, base_op):
#        q = self.dbSession.query(PendingUpdateDeletes).filter(PendingUpdateDeletes.id==base_op)
#        q = q.with_lockmode('read')
#        
#        read_locked = q.all()
#        
#        if len(read_locked) == 0:
#            pud = PendingUpdateDeletes()
#            pud.id = base_op
#            pud.state = PendingUpdateState.ACTIVE
#            self.dbSession.add(pud)
#            self.dbSession.commit()
#        elif read_locked[0].state != PendingUpdateState.ACTIVE:
#            read_locked[0].state = PendingUpdateState.ACTIVE
#            self.dbSession.merge(pud)
#            self.dbSession.commit()
        
        sql = "INSERT INTO Pending_Insert_Deletes VALUES (:base_op, " \
                + str(PendingUpdateState.ACTIVE) + ") ON DUPLICATE KEY UPDATE state=" \
                + str(PendingUpdateState.ACTIVE)
        
        self.dbSession.execute(sql, {'base_op': base_op})
    
    def pending_updates_deletes_finished(self, pending_ud):
        self.dbSession.delete(pending_ud)
        
    def evaluationOrderToQuery(self, q, evaluation_order):
        if evaluation_order == "positive":
            return q.order_by(Opinion.evaluation.desc())
        elif evaluation_order == "negative":
            return q.order_by(Opinion.evaluation)
        else:
            return q
    
    def orderToQuery(self, order, q):
        if order == 'newest':
            return q.order_by(Opinion.creation_time.desc())
        elif order == 'oldest':
            return q.order_by(Opinion.creation_time.asc())
        elif order == 'most_positive':
            return q.order_by(Opinion.positive_attention.desc())
        elif order == 'most_negative':
            return q.order_by(Opinion.negative_attention.desc())
        elif order == 'most_attended':
            # (op.nested_set_right - op.nested_set_left - 1) / 2
            return q.order_by( desc(Opinion.nested_set_right - Opinion.nested_set_left) )
        elif order == 'general_attention_desc':
            return q.order_by( desc(Opinion.positive_attention - Opinion.negative_attention ))
        else:
            return None
    
    def getSubOpinions(self, opinion, tree_order, order, maxDepth, offset, limit, evaluation_order, skip_no_content):
        if maxDepth > 0 and opinion.nested_set_right > opinion.nested_set_left + 1:
            q = self.dbSession.query(Opinion)
            q = q.options(joinedload(Opinion.creator))
            q = q.filter(Opinion.nested_set_left.between(opinion.nested_set_left + 1, opinion.nested_set_right - 1), Opinion.base_op_id == opinion.base_op_id)
            
#            if skip_no_content:
#                q = q.filter(Opinion.has_content == 1)
                             
            if tree_order == 'flat':
                q = self.evaluationOrderToQuery(q, evaluation_order)
                q = self.orderToQuery(order, q)
            elif tree_order == 'breadth_first':
                q = q.filter(Opinion.tree_depth <= opinion.tree_depth + maxDepth)
                q = q.order_by(Opinion.tree_depth)
                q = self.evaluationOrderToQuery(q, evaluation_order)
                q = self.orderToQuery(order, q)
            elif tree_order == 'depth_first':
                q = q.filter(Opinion.tree_depth <= opinion.tree_depth + maxDepth)
                if order == 'newest':
                    if maxDepth == 1:
                        q = self.evaluationOrderToQuery(q, evaluation_order)
                    q = q.order_by(Opinion.nested_set_right.desc())
                elif order == 'oldest':
                    if maxDepth == 1:
                        q = self.evaluationOrderToQuery(q, evaluation_order)
                    q = q.order_by(Opinion.nested_set_left.asc())
                else:
                    if maxDepth == 1:
                        q = self.evaluationOrderToQuery(q, evaluation_order)
                        q = self.orderToQuery(order, q)
                    else:
                        raise ValueError("Cannot request with single query sub-opinions with depth_first from more than 1 level and not ordered by creation time")
                    
            if not q:
                return None
            
            return q.offset(offset).limit(limit).all()
        else:
            return []
    
    def getBatchOpinions(self, base_op_id, pre_order_ranges, tree_depth_min, tree_depth_max, skip_empty):
        q = self.dbSession.query(Opinion)
        q = q.options(joinedload(Opinion.creator))
        q = q.filter(Opinion.base_op_id == base_op_id)
        q = q.filter(Opinion.tree_depth.between(tree_depth_min, tree_depth_max))
        
        #if skip_empty:
        #    q = q.filter(Opinion.has_content == 1)
        
        rangeFilter = None
        
        if len(pre_order_ranges) == 1:
            po_left, po_righ = pre_order_ranges[0]
            rangeFilter = Opinion.nested_set_left.between(po_left, po_righ)
        elif len(pre_order_ranges) > 1:
            po_left1, po_righ1 = pre_order_ranges[0]
            po_left2, po_righ2 = pre_order_ranges[1]
            rangeFilter = or_(Opinion.nested_set_left.between(po_left1, po_righ1),
                              Opinion.nested_set_left.between(po_left2, po_righ2))
            for po_range in pre_order_ranges[2:]:
                po_left, po_righ = po_range
                rangeFilter.append(Opinion.nested_set_left.between(po_left, po_righ))
        assert rangeFilter is not None
        q = q.filter(rangeFilter)
        
        return q.all()
        
    def get_batch_opinions_by_ids(self, op_ids):
        q = self.dbSession.query(Opinion)
        q = q.filter(Opinion.id.in_(op_ids))
        
        return q.all()
    
    def getUserOpinions(self, userExtId, userId, base_op_id, isNonNeutralOnly, order, offset, limit):
        if userId:
            q = self.dbSession.query(Opinion)
            q = q.options(joinedload(Opinion.creator))
            q = q.filter(Opinion.base_op_id == base_op_id, Opinion.creator_id == userId)
        else:
            q = self.dbSession.query(Opinion).join(User, User.id == Opinion.creator_id)
            q = q.options(joinedload(Opinion.creator))
            q = q.filter(Opinion.base_op_id == base_op_id, User.external_id == userExtId)
        
        if isNonNeutralOnly:
            q = q.filter(Opinion.evaluation != OpinionEvaluations.NEUTRAL)
        
        q = self.orderToQuery(order, q)
        if not q:
            return False, -1
        
        return self.paginateResults(q, offset, limit)
    
    def lock_user(self, user_id):
        q = self.dbSession.query(User).filter(User.id == user_id.bytes).with_lockmode("update");
        result = q.all()
        if result and len(result) > 0:
            return result[0]
        else:
            return None
    
    def getAllBaseOpinions(self, order, offset, limit):
        q = self.dbSession.query(Opinion).options(joinedload(Opinion.creator))
        q = q.filter(Opinion.base_op_id == Opinion.id)
        
        q = self.orderToQuery(order, q)
        if not q:
            return False, -1
        
        return self.paginateResults(q, offset, limit)
    
    def fillOpinionTexts(self, opinions, contentLength):
        if len(opinions):
            q = self.dbSession.query(TextStorage.id, TextStorage.title, 
                                     TextStorage.external_data, func.substr(TextStorage.content, 1, contentLength))
            
            q = q.filter(TextStorage.id.in_( [op.id for op in opinions] ))
            
            allTexts = q.all()
            for opId, title, extData, text in allTexts:
                for op in opinions:
                    if op.id == opId:
                        op.textData = text
                        op.title = title
                        op.external_data = extData
                        

            
    def createView(self, view):
        view.setUUID(self.generateUUID())
        self.dbSession.add(view)
    
    def deleteView(self, viewId):
        numDeleted = self.dbSession.query(ViewEnt).filter(ViewEnt.external_id == viewId).delete(synchronize_session=False)
        return numDeleted
    
    def getViewById(self, viewId):
        return self.dbSession.query(ViewEnt).filter(ViewEnt.external_id == viewId).first()
    
    def addOpinionsToView(self, view, opinionIds):
        query = cStringIO.StringIO()
        query.write("INSERT INTO View_Opinions (view_id, opinion_id)" 
                    + " SELECT :viewId, op_id FROM Opinion WHERE op_id IN ( ")
        
        params = {'viewId' : view.id}
        
        opIndex = 0
        for opId in opinionIds:
            if opIndex > 0:
                query.write(', ')
            paramKey = 'op' + str(opIndex)
            query.write(':' + paramKey)
            opIndex += 1
            params[paramKey] = opId
        
        query.write(")")
        
        self.dbSession.execute(query.getvalue(), params)
        
        query.close()
        
        q = self.dbSession.query(ViewOpinion.opinion_id).filter(ViewOpinion.view_id==view.id, ViewOpinion.opinion_id.in_(opinionIds))
        result = q.all()
        return [res[0] for res in result]
    
    def getViewOpinions(self, view, order, offset, limit):
        q = self.dbSession.query(Opinion).options(joinedload(Opinion.creator))
        q = q.join(ViewOpinion, ViewOpinion.opinion_id == Opinion.id)
        q = q.filter(ViewOpinion.view_id == view.id)
        
        q = self.orderToQuery(order, q)
        if not q:
            return False, -1
        
        return self.paginateResults(q, offset, limit)